--    Author    : ChenErHao
--    Name      : DM.MID_MTH_BAD_LOAN_DOWN_AMOUNT_TWO.HQL
--    Functions : 风险-不良贷款向下迁徙量_上月
--    Purpose   :
--    Revisions or Comments
--    VER        DATE        AUTHOR           DESCRIPTION
--   ---------  ----------  ---------------  ------------------------------------
--    1.0        2019-01-23  ChenErHao       1.CREATE THE PROCEDURE
--
INSERT OVERWRITE TABLE DM.MID_MTH_BAD_LOAN_DOWN_AMOUNT_TWO PARTITION (FISCAL_MTH_DIMNSN_ID = '#V_DATA_MONTH#') 
 SELECT
    B.ORG_ID,
    B.CUSTOMER_TYPE,
    B.PRODUCT_CATEGORY,
    B.LOAN_PERIOD,
    B.LPIC,
    B.LOANS_ACTUALLY,
    B.ENTERPRISE_SCALE,
    B.ENTERPRISE_INVESTOR,
    B.GUARANTY_STYLE,
    B.LOAN_STATUS,
    B.LOAN_QUALITY,
    B.AREA_NO_ID,
    nvl(SUM(B.LOAN_BALANCE),0)      LOAN_BALANCE,
    nvl(SUM(B.LOAN_OCCURRENCE_LEVEL),0) LOAN_BALANCE_RATE
FROM
    (
        SELECT
            LOAN_IOUS_CODE
        FROM
            EDW.LOAB
        WHERE
            LOAN_QUALITY IN ('FQ01','FQ02')
        AND DATA_DATE='#V_LAST_2M_END_DATE#' --上上月
        ) A,
    (
        SELECT
            LOAN_IOUS_CODE,
            BELONG_AREA_CODE        AREA_NO_ID, 
            CASE WHEN B.ORG_LVL_ID_2 IS NULL THEN '9999'
                 WHEN B.ORG_LVL_ID_2='' THEN '9999'
                 ELSE B.ORG_LVL_ID_2 END   ORG_ID,
            CUSTOMER_TYPE         CUSTOMER_TYPE,--客户类型
            CASE WHEN C.LOAN_PRO_ID_1 IS NULL THEN '9999'
                 WHEN C.LOAN_PRO_ID_1 ='' THEN '9999'
                 ELSE C.LOAN_PRO_ID_1 END PRODUCT_CATEGORY,--贷款产品类别
            LOANS_PERIOD           LOAN_PERIOD,--贷款期限,
            NVL(LPIC_SUB,'9999') LPIC,--企业分行业
            CASE WHEN G.LOAN_ACTUALLY_ID_1 IS NULL THEN '9999'
                 WHEN G.LOAN_ACTUALLY_ID_1 ='' THEN '9999'
                 ELSE G.LOAN_ACTUALLY_ID_1 END LOANS_ACTUALLY,--行业投向
            nvl(ENTERPRISE_SCALE,'9999') ENTERPRISE_SCALE,--企业规模
            CASE WHEN E.INVESTOR_FINICL_ID_2 IS NULL THEN '9999'
                 WHEN E.INVESTOR_FINICL_ID_2 ='' THEN '9999'
                 ELSE E.INVESTOR_FINICL_ID_2 END  ENTERPRISE_INVESTOR,--企业出资人经济成分二级编码 
            CASE WHEN F.LOAN_INSUR_ID_1 IS NULL THEN '9999'
                 WHEN F.LOAN_INSUR_ID_1 ='' THEN '9999'
                 ELSE F.LOAN_INSUR_ID_1 END   GUARANTY_STYLE,-- 贷款担保方式一级编码
            nvl(LOAN_STATUS,'9999')           LOAN_STATUS,--贷款状态
            nvl(LOAN_QUALITY,'9999')          LOAN_QUALITY,--贷款质量
            LOAN_BALANCE,
            LOAN_OCCURRENCE_LEVEL
        FROM
        (SELECT LOAN_IOUS_CODE,BELONG_AREA_CODE,DATA_ORG_CODE,PRODUCT_CATEGORY,LPIC_SUB,LOANS_ACTUALLY,ENTERPRISE_INVESTOR,GUARANTY_STYLE,
            CUSTOMER_TYPE,LOANS_PERIOD,ENTERPRISE_SCALE,LOAN_STATUS,LOAN_QUALITY,LOAN_BALANCE,LOAN_OCCURRENCE_LEVEL
            FROM EDW.LOAB     WHERE
            LOAN_QUALITY IN ('FQ03','FQ04','FQ05')
        AND DATA_DATE='#V_LAST_1M_END_DATE#' --上月
        ) A1 
        LEFT JOIN DMCODE.T_ORG_BIZ_LVL   B ON CASE WHEN a1.DATA_ORG_CODE IS NULL THEN CONCAT('org_code_hive',RAND()) else A1.DATA_ORG_CODE end= B.ORG_ID  --金融机构
        LEFT JOIN DMCODE.T_LOAN_PRO_TYPE C ON CASE WHEN a1.PRODUCT_CATEGORY IS NULL THEN CONCAT('org_code_hive',RAND()) else A1.PRODUCT_CATEGORY end= C.LOAN_PRO_ID --贷款产品类别
        LEFT JOIN DMCODE.T_LOANS_ACTUALLY G ON CASE WHEN a1.LOANS_ACTUALLY IS NULL THEN CONCAT('org_code_hive',RAND()) else A1.LOANS_ACTUALLY end=G.LOAN_ACTUALLY_ID   --贷款行业投向
        LEFT JOIN DMCODE.T_INVESTOR_FINICL_TYPE_TRANS E ON CASE WHEN a1.ENTERPRISE_INVESTOR IS NULL THEN CONCAT('org_code_hive',RAND()) else A1.ENTERPRISE_INVESTOR end= E.INVESTOR_FINICL_ID --企业出资人经济成分
        LEFT JOIN DMCODE.T_LOAN_INSUR_TYPE F ON CASE WHEN a1.GUARANTY_STYLE IS NULL THEN CONCAT('org_code_hive',RAND()) else A1.GUARANTY_STYLE end=F.LOAN_INSUR_ID   -- 贷款担保方式    
         ) B
WHERE
    A.LOAN_IOUS_CODE=B.LOAN_IOUS_CODE
GROUP BY
    B.ORG_ID,
    B.AREA_NO_ID,
    B.CUSTOMER_TYPE,
    B.PRODUCT_CATEGORY,
    B.LOAN_PERIOD,
    B.LPIC,
    B.LOANS_ACTUALLY,
    B.ENTERPRISE_SCALE,
    B.ENTERPRISE_INVESTOR,
    B.GUARANTY_STYLE,
    B.LOAN_STATUS,
    B.LOAN_QUALITY
;